package com.mairuide.phone.abnormalphone;

import com.mairuide._frame.base.BaseRepository;
import com.mairuide._frame.base.BaseServiceProxy;
import com.mairuide._frame.utils.StringUtils;
import com.mairuide._frame.utils.Testtrans;
import com.mairuide.httpclient.BaseSite;
import com.mairuide.phone.PhoneRecord;
import com.mairuide.phone.callanalysis.entity.PhoneMapDetail;
import com.mairuide.phone.eachothercall.EachOtherCallAnalysisPersonal;
import org.hibernate.SQLQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.sql.Timestamp;
import java.util.*;
import java.util.regex.Pattern;

/**
 * 异常号码分析service
 * @author mh
 *
 */
@Service
@Transactional(readOnly = true)
public class AbnormalPhoneService extends BaseServiceProxy<PhoneRecord> {
	@PersistenceContext
	private EntityManager entityManager;
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Resource
	private BaseSite baseSite;

	@Override
	protected EntityManager getEntityManager() {
		return entityManager;
	}

	@Override
	protected BaseRepository<PhoneRecord> getBaseRepository() {
		return null;
	}
	
	/**
	 * 异常号码分析入口
	 * @param eocap
	 * @return
	 */
	public List<AbnormalPhone> getExceptionPhoneAnalysisInit(AbnormalPhoneParams eocap) {
		List<AbnormalPhone> aps = new ArrayList<AbnormalPhone>();
		List<PhoneRecord> prs = findAbnormalPhone(eocap);
		List<String> partyNumbers = new ArrayList<>();
		String[] strs = eocap.getCheckedPhone().split(",");
		if(null != prs && prs.size() > 0) {
			for(PhoneRecord pr : prs) {
				//如果集合不存在则添加被叫号码
				if(!partyNumbers.contains(pr.getPartyNumber())) {
					partyNumbers.add(pr.getPartyNumber());
				}
			}
			for(String pn : partyNumbers) {//以异常联系人为主进行核算
				List<EachOtherCallAnalysisPersonal> ecaps = new ArrayList<EachOtherCallAnalysisPersonal>();
				List<Timestamp> beginTimes = new ArrayList<Timestamp>();
				List<Timestamp> endTimes = new ArrayList<Timestamp>();
				AbnormalPhone ap = new AbnormalPhone();
				for(String phoneNumber : strs) {
					if(StringUtils.isEmpty(phoneNumber)) {
						continue;
					}
					Integer callNumber = 0;//通话次数
					Integer callSeconds = 0;//通话秒数
					Timestamp beginTime = null;//开始时间
					int ix = 0;
					for(PhoneRecord pr : prs) {
						if(pr.getServiceNumber().equals(phoneNumber) && pr.getPartyNumber().equals(pn)) {
							callNumber ++;//通话次数
							if(StringUtils.isNotEmpty(pr.getTalkTime())) {
								callSeconds += Integer.parseInt(pr.getTalkTime());//通话秒数
							}
							if(ix == 0) {//开始时间
								beginTime = pr.getStartingTime();
							}
							ix++;
						}
					}
					EachOtherCallAnalysisPersonal ecap = new EachOtherCallAnalysisPersonal();
					ecap.setSuspectPhonePersonal(phoneNumber);
					ecap.setCallNumber(callNumber);
					ecap.setCallSeconds(callSeconds);
					ecap.setBeginTime(beginTime);
					Timestamp endTime = StringUtils.addM4TimeStamp(beginTime, callSeconds);//计算结束时间
					ecap.setEndTime(endTime);
					beginTimes.add(beginTime);
					endTimes.add(endTime);
					ecaps.add(ecap);
				}
				//总通话次数
				Integer xtotalCallNumber = 0;
				//总通话秒数
				Integer xtotalCallSeconds = 0;
				for(EachOtherCallAnalysisPersonal ecap : ecaps) {
					xtotalCallNumber += ecap.getCallNumber();
					xtotalCallSeconds += ecap.getCallSeconds();
				}
				//总开始时间
				Timestamp totalBeginTime = StringUtils.getMinBeginTime(beginTimes);
				//总结束时间
				Timestamp totalEndTime = StringUtils.getMaxBeginTime(endTimes);
				ap.setXtotalBeginTime(totalBeginTime);
				ap.setXtotalEndTime(totalEndTime);
				ap.setExceptionPhone(pn);
				ap.setXtotalCallNumber(xtotalCallNumber);
				ap.setXtotalCallSeconds(xtotalCallSeconds);
				ap.setEcaps(ecaps);
				aps.add(ap);
			}
//			}
		}
		return aps;
	}

	public PhoneRecord getAbnormalPhone(String caseId){
		String str = "SELECT " +
				"	TPR.RECORD_ID AS recordId,\n" +
				"	TPR.CASEMANAGE_ID AS casemanageId,\n" +
				"	TPR.STARTING_TIME AS startingTime,\n" +
				"	TPR.SERVICE_NUMBER AS serviceNumber,\n" +
				"	TPR.SERVICE_USER_INFORMATION AS serviceUserInformation,\n" +
				"	TPR.SERVICE_LOCATION AS serviceLocation,\n" +
				"	TPR.CARD_NUMBER AS cardNumber,\n" +
				"	TPR.PARTY_NUMBER AS partyNumber,\n" +
				"	TPR.PARTY_USER_INFORMATION AS partyUserInformation,\n" +
				"	TPR.PARTY_LOCATION AS partyLocation,\n" +
				"	TPR.TALK_TIME AS talkTime,\n" +
				"	TPR.CALL_TYPE AS callType,\n" +
				"	TPR.LAC AS lac,\n" +
				"	TPR.CID AS cid,\n" +
				"	TPR.BASE_STATION AS baseStation,\n" +
				"	TPR.MSC AS msc,\n" +
				"	TPR.CITY AS city,\n" +
				"	TPR.THIRD_NUMBER AS thirdNumber,\n" +
				"	TPR.THIRD_NUMBER_INFORMATION AS thirdNumberInformation,\n" +
				"	TPR.THIRD_NUMBER_LOCATION AS thirdNumberLocation,\n" +
				"	TPR.OPERATOR_CODE AS operationCode,\n" +
				"	TPR.OPERATOR_CODE_INFORMATION AS operationCodeInformation,\n" +
				"	TPR.BUSINESS_KEY AS businessKey "+
				" from TB_PHONE_RECORDING TPR WHERE CASEMANAGE_ID = '"+caseId+"' and ROWNUM=1";
		PhoneRecord ap = jdbcTemplate.queryForObject(str,PhoneRecord.class);
		return ap;
	}
	
	/**
	 * 异常号码分析查询
	 * @param eocap
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<PhoneRecord> findAbnormalPhone(AbnormalPhoneParams eocap) {
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT\r\n" + 
				"  TPR.RECORD_ID AS recordId,\r\n" + 
				"  TPR.CASEMANAGE_ID AS casemanageId,\r\n" + 
				"  TPR.STARTING_TIME AS startingTime,\r\n" + 
				"  TPR.SERVICE_NUMBER AS serviceNumber,\r\n" + 
				"  TPR.SERVICE_USER_INFORMATION AS serviceUserInformation,\r\n" + 
				"  TPR.SERVICE_LOCATION AS serviceLocation,\r\n" + 
				"  TPR.CARD_NUMBER AS cardNumber,\r\n" + 
				"  TPR.PARTY_NUMBER AS partyNumber,\r\n" + 
				"  TPR.PARTY_USER_INFORMATION AS partyUserInformation,\r\n" + 
				"  TPR.PARTY_LOCATION AS partyLocation,\r\n" + 
				"  TPR.TALK_TIME AS talkTime,\r\n" + 
				"  TPR.CALL_TYPE AS callType,\r\n" + 
				"  TPR.LAC AS lac,\r\n" + 
				"  TPR.CID AS cid,\r\n" + 
				"  TPR.BASE_STATION AS baseStation,\r\n" + 
				"  TPR.MSC AS msc,\r\n" + 
				"  TPR.CITY AS city,\r\n" + 
				"  TPR.THIRD_NUMBER AS thirdNumber,\r\n" + 
				"  TPR.THIRD_NUMBER_INFORMATION AS thirdNumberInformation,\r\n" + 
				"  TPR.THIRD_NUMBER_LOCATION AS thirdNumberLocation,\r\n" + 
				"  TPR.OPERATOR_CODE AS operationCode,\r\n" + 
				"  TPR.OPERATOR_CODE_INFORMATION AS operationCodeInformation,\r\n" + 
				"  TPR.BUSINESS_KEY AS businessKey\r\n" + 
				"FROM\r\n" + 
				"  TB_PHONE_RECORDING TPR\r\n" + 
				"WHERE\r\n" + 
				"TPR.IS_DELETE=0 AND TPR.CALL_TYPE IN(0,1) ");
		
		selectSql.append("AND TPR.PARTY_NUMBER NOT IN (");
		selectSql.append("SELECT DISTINCT T1.PARTY_NUMBER FROM TB_PHONE_RECORDING T1 WHERE ");
		selectSql.append("1 = 1 AND T1.IS_DELETE = 0 AND T1.CALL_TYPE IN (0, 1) ");
		/* 主叫号码 、被叫号码*/
		selectSql.append(" AND (1=0 ");
		if(StringUtils.isNotEmpty(eocap.getCheckedPhone())) {
			String phones = "";
			for(String phone : eocap.getCheckedPhone().split(",")) {
				if(StringUtils.isNotEmpty(phone)) {
					phones = phones+"'"+phone+"',";
				}
			}
			if(phones.endsWith(",")) {
				phones = phones.substring(0,phones.length()-1);
			}
			if(phones.length()>0) {
				selectSql.append(" OR T1.SERVICE_NUMBER IN ("+phones+")");
			}
		}
		selectSql.append(" ) ");
		if(eocap.getBeforeAndAfter().equals(1)) {
			selectSql.append(" AND T1.STARTING_TIME < to_date('"+eocap.getStartTime()+"','yyyy-mm-dd hh24:mi:ss') ) ");
		}else {
			selectSql.append(" AND T1.STARTING_TIME > to_date('"+eocap.getStartTime()+"','yyyy-mm-dd hh24:mi:ss') ) ");
		}
		
		/* 案件id */
		if(StringUtils.isNotEmpty(eocap.getCaseId())) {
			selectSql.append("AND TPR.CASEMANAGE_ID ='"+eocap.getCaseId()+"' ");
		}
		/* 主叫号码 、被叫号码*/
		selectSql.append(" AND (1=0 ");
		if(StringUtils.isNotEmpty(eocap.getCheckedPhone())) {
			String phones = "";
			for(String phone : eocap.getCheckedPhone().split(",")) {
				if(StringUtils.isNotEmpty(phone)) {
					if(StringUtils.isNotEmpty(phone)) {
						phones = phones+"'"+phone+"',";
					}
				}
			}
			if(phones.endsWith(",")) {
				phones = phones.substring(0,phones.length()-1);
			}
			if(phones.length()>0) {
				selectSql.append(" OR TPR.SERVICE_NUMBER IN ("+phones+")");
			}
		}
		selectSql.append(" ) ");
		if(eocap.getBeforeAndAfter().equals(1)) {
			selectSql.append(" AND TPR.STARTING_TIME > to_date('"+eocap.getStartTime()+"','yyyy-mm-dd hh24:mi:ss') ");
		}else {
			selectSql.append(" AND TPR.STARTING_TIME < to_date('"+eocap.getStartTime()+"','yyyy-mm-dd hh24:mi:ss') ");
		}
		selectSql.append(" ORDER BY TPR.STARTING_TIME ");
		Query query = getEntityManager().createNativeQuery(selectSql.toString());
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(PhoneRecord.class));
		//封装返回数据
		return query.getResultList();
	}
	
	/**
	 * 相互通话详情查询
	 * @param
	 * @return
	 */
	public Page<PhoneRecord> abnormalDetails(AbnormalPhoneParams app, Pageable page,String sortName,String sortOrder) {
		List<Object> params = new LinkedList<Object>();
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT\r\n" + 
				"  TPR.RECORD_ID AS recordId,\r\n" + 
				"  TPR.CASEMANAGE_ID AS casemanageId,\r\n" + 
				"  TPR.STARTING_TIME AS startingTime,\r\n" + 
				"  TPR.SERVICE_NUMBER AS serviceNumber,\r\n" + 
				"  TPR.SERVICE_USER_INFORMATION AS serviceUserInformation,\r\n" + 
				"  TPR.SERVICE_LOCATION AS serviceLocation,\r\n" + 
				"  TPR.CARD_NUMBER AS cardNumber,\r\n" + 
				"  TPR.PARTY_NUMBER AS partyNumber,\r\n" + 
				"  TPR.PARTY_USER_INFORMATION AS partyUserInformation,\r\n" + 
				"  TPR.PARTY_LOCATION AS partyLocation,\r\n" + 
				"  TPR.TALK_TIME AS talkTime,\r\n" + 
				"  TPR.CALL_TYPE AS callType,\r\n" + 
				"  TPR.LAC AS lac,\r\n" + 
				"  TPR.CID AS cid,\r\n" +
				"  TPR.ADDRESS AS address,\r\n" +
				"  TPR.BASE_STATION AS baseStation,\r\n" + 
				"  TPR.MSC AS msc,\r\n" + 
				"  TPR.CITY AS city,\r\n" + 
				"  TPR.THIRD_NUMBER AS thirdNumber,\r\n" + 
				"  TPR.THIRD_NUMBER_INFORMATION AS thirdNumberInformation,\r\n" + 
				"  TPR.THIRD_NUMBER_LOCATION AS thirdNumberLocation,\r\n" + 
				"  TPR.OPERATOR_CODE AS operationCode,\r\n" + 
				"  TPR.OPERATOR_CODE_INFORMATION AS operationCodeInformation,\r\n" + 
				"  TPR.BUSINESS_KEY AS businessKey\r\n");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append("FROM\r\n" + 
				"  TB_PHONE_RECORDING TPR\r\n" + 
				"WHERE\r\n" + 
				"TPR.IS_DELETE = 0 AND TPR.CALL_TYPE IN(0,1) ");
		
		/* 案件id */
		if(StringUtils.isNotEmpty(app.getCaseId())) {
			fromWhereSql.append("AND TPR.CASEMANAGE_ID =? ");
			params.add(app.getCaseId());
		}
		/* 主叫被叫 */
		if(StringUtils.isNotEmpty(app.getSuspectPhone())) {
			fromWhereSql.append("AND TPR.SERVICE_NUMBER ='"+app.getSuspectPhone()+"' ");
		}
		if(StringUtils.isNotEmpty(app.getSuspectPhonePersonal())) {
			fromWhereSql.append("AND TPR.PARTY_NUMBER ='"+app.getSuspectPhonePersonal()+"' ");
		}
		/* 通话时间 */
		if(StringUtils.isNotEmpty(app.getStartTime())) {
			if(app.getBeforeAndAfter().equals(1)) {
				fromWhereSql.append(" AND TPR.STARTING_TIME >= to_date('"+app.getStartTime()+" 00:00:00','yyyy-mm-dd hh24:mi:ss') ");
			}else {
				fromWhereSql.append(" AND TPR.STARTING_TIME <= to_date('"+app.getStartTime()+" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
			}
		}
		
		StringBuffer orderBySql = new StringBuffer();
		if(StringUtils.isNotEmpty(sortName)) {
			orderBySql.append(" ORDER BY "+sortName+ " " +sortOrder);
		}else {
			orderBySql.append(" ORDER BY TPR.STARTING_TIME DESC ");
		}
		//封装返回数据

		Page<PhoneRecord> pageCall = super.findPageBySql(PhoneRecord.class,selectSql.toString(), fromWhereSql.toString(), orderBySql.toString(), params, page);
		getAddress(pageCall);
		return pageCall;
	}

	private void getAddress(Page<PhoneRecord> pagePh){
		Set<PhoneMapDetail> phoneMapDetailList = new HashSet<>();
		if(pagePh!=null && pagePh.getContent()!=null) {
			for(PhoneRecord pr:pagePh.getContent()) {
				if(StringUtils.isEmpty(pr.getAddress()) && StringUtils.isNotEmpty(pr.getCid()) && StringUtils.isNotEmpty(pr.getLac())) {
					PhoneMapDetail detail = new PhoneMapDetail();
					detail.setCID(StringUtils.isEmpty(pr.getCid())?"0":pr.getCid());
					detail.setLAC(StringUtils.isEmpty(pr.getLac())?"0":pr.getLac());
					detail.setLATB(StringUtils.isEmpty(pr.getLatb())?"0":pr.getLatb());
					detail.setLNGB(StringUtils.isEmpty(pr.getLngb())?"0":pr.getLngb());
					detail.setADDRESS(StringUtils.isEmpty(pr.getAddress())?"0":pr.getAddress());
					detail.setSERVICE_NUMBER(pr.getServiceNumber());
					phoneMapDetailList.add(detail);
				}
			}
		}
		List<PhoneMapDetail> arrayList = new ArrayList<>(phoneMapDetailList);
		if(arrayList.size()>0) {
			List arrayList_hava = baseSite.getMap(arrayList);
			if(arrayList_hava!=null && arrayList_hava.size()>0) {
				for(PhoneMapDetail detail:(List<PhoneMapDetail>)arrayList_hava) {
					for(PhoneRecord pr:pagePh.getContent()) {
						if(StringUtils.isEmpty(pr.getAddress()) && StringUtils.isNotEmpty(pr.getCid()) && StringUtils.isNotEmpty(pr.getLac())) {
							if(pr.getCid().equals(detail.getCID()) && pr.getLac().equals(detail.getLAC())) {
								pr.setAddress(detail.getADDRESS());
							}
						}
					}
				}
			}
		}
	}

	public List<EachOtherCallAnalysisPersonal> getEachOtherCallList(List<AbnormalPhone> eca,String phone){
		List<EachOtherCallAnalysisPersonal> list = new ArrayList<>();
		for(AbnormalPhone ap:eca){
			for (EachOtherCallAnalysisPersonal ecap : ap.getEcaps()) {
				if (phone.equals(ecap.getSuspectPhonePersonal())){
					list.add(ecap);
					break;
				}
			}
		}
		return list;
	}

}
